const router = require('koa-router')()
const db = require('../utils/db')

router.prefix('/msbd')

// 获取数据、分页、搜索、排序
router.get('/get-msbd', async ctx => {
    const { currentPage = 0, pageSize = 20, query, sortBy = 'id', order = "asc" } = ctx.query
    let sql = `select * from msbd`
    let classifySql = `select * from classify`
    // 搜索
    if (query) {
        sql += ` where title like '%${query}%'`
    }

    // 排序
    if (order) {
        sql += ` order by ${sortBy} ${order}`
    }

    // 分页
    sql += ` limit ${currentPage * pageSize},${pageSize}`

    // 获取总条数
    let totalSql = `select count(*)as total from msbd`
    if (query) {
        totalSql += ` where title like '%${query}%'`
    }
    const totalR = await db.doSQL(totalSql)
    const total = totalR[0].total
    const r = await db.doSQL(sql)
    const classifyR = await db.doSQL(classifySql)
    ctx.body = db.doResponse({ message: '获取数据成功', data: {data:r,classify:classifyR}, total})
})

// 删除
router.get('/delete-msbd', async ctx => {
    const { id } = ctx.query
    const sql = `delete from msbd where id in (${id})`
    const params = [id]
    const r = await db.doSQL(sql, params)
    if (r.affectedRows > 0) {
        ctx.body = db.doResponse({ message: '删除数据成功', data: r })
    } else {
        ctx.body = db.doResponse({ message: '删除数据失败', errorCode: 5003, success: false, data: r })
    }
    // console.log(r)
})

// 修改
router.post('/update-msbd', async ctx => {
    let { title, content, author = 'situ', type, cid, time, id } = ctx.request.body
    const getMsbdSql = `select * from classify`
    const getMsbdR = await db.doSQL(getMsbdSql)
    getMsbdR.forEach(item => {
        if (type == item.name) {
            cid = item.id
        }
    })
    const sql = `update msbd set title=?,content=?,author=?,type=?,cid=?,time=? where id=?`
    const params = [title, content, author, type, cid, time, id]
    const r = await db.doSQL(sql, params)
    if (r.affectedRows > 0) {
        ctx.body = db.doResponse({ message: '修改数据成功', data: r })
    } else {
        ctx.body = db.doResponse({ message: '修改数据失败', errorCode: 5002, success: false, data: r })
    }
})

// 新增
router.post('/save-msbd', async ctx => {
    let { title, content, author = 'situ', type, cid, time } = ctx.request.body
    const getMsbdSql = `select * from classify`
    const getMsbdR = await db.doSQL(getMsbdSql)
    getMsbdR.forEach(item => {
        if (type == item.name) {
            cid = item.id
        }
    })
    const sql = `insert into msbd (title,content,author,type,cid,time) values (?,?,?,?,?,?)`
    const params = [title, content, author, type, cid, time]
    const r = await db.doSQL(sql, params)
    if (r.affectedRows > 0) {
        ctx.body = db.doResponse({ message: '新增数据成功', data: r })
    } else {
        ctx.body = db.doResponse({ message: '新增数据失败', errorCode: 5001, success: false, data: r })
    }
})

router.get('/', async function (ctx) {
    ctx.body = 'msbd'
})

module.exports = router